package com.junsi.controller;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.junsi.bean.Client;
import com.junsi.bean.Department;
import com.junsi.bean.Rank;
import com.junsi.bean.Record;
import com.junsi.bean.Staff;
import com.junsi.bean.Usertracking;
import com.junsi.service.RankService;
import com.junsi.service.RecordService;
import com.junsi.service.StaffSerivce;
import com.junsi.service.UsertrackingService;
import com.junsi.util.FileUtil;
import com.junsi.util.InteractiveUtil;
import com.junsi.util.PageUtil;
import com.junsi.util.UsertrackUtil;


/**
 * 用户跟踪控制层
 * @author Administrator
 *
 */
@Controller
@RequestMapping("/Usertracking")
public class UsertrackingController {
	
	@Autowired
	private RecordService recordService ;
	@Autowired
	private UsertrackingService usertrackingService;
	@Autowired
	private StaffSerivce staffSerivce;
	@Autowired
	private RankService rankService;
   
    Logger logger=Logger.getRootLogger();
    
    /**
	 * 客户总页数,页码未审批
	 * @param staff
	 * @param file
	 * @return
	 */
	@RequestMapping("/count")
	 public ModelAndView count(PageUtil page,HttpServletResponse response,HttpServletRequest request,String select){
		ModelAndView modelAndView=new ModelAndView();
		String idString = (String) request.getSession().getAttribute("staffs");
		String[] id = idString.split(","); 
		page.setPageSize(5);
		String statu="0";
		int count = usertrackingService.findCount(id,statu);
		page.setRowCount(count);
		modelAndView.addObject("PageCount", page.getPageCount());
		modelAndView.addObject("PageNum", page.getPageNum());
		
		modelAndView.addObject("select", select);

		//显示新增时下拉框里的名字选项
		List<Record> records=recordService.listAll();
		List<Rank> rank=rankService.listAll();
 		request.getSession().setAttribute("records", records);
 		request.getSession().setAttribute("rank", rank);
		
		modelAndView.setViewName("forward:/userxiansuo/html/kehuxinxi.jsp");
		return modelAndView;
	}
	
	 /**
		 * 企业总页数,页码未审批
		 * @param staff
		 * @param file
		 * @return
		 */
		@RequestMapping("/count1")
		 public ModelAndView count1(PageUtil page,HttpServletResponse response,HttpServletRequest request,String select){
			ModelAndView modelAndView=new ModelAndView();
			String idString = (String) request.getSession().getAttribute("staffs");
			String[] id = idString.split(","); 
			page.setPageSize(5);
			String statu="1";
			int count = usertrackingService.findCount(id,statu);
			page.setRowCount(count);
			modelAndView.addObject("PageCount", page.getPageCount());
			modelAndView.addObject("PageNum", page.getPageNum());
			
			modelAndView.addObject("select", select);

			//显示新增时下拉框里的名字选项
			List<Record> records=recordService.listAll();
			List<Rank> rank=rankService.listAll();
	 		request.getSession().setAttribute("records", records);
	 		request.getSession().setAttribute("rank", rank);
			
			modelAndView.setViewName("forward:/userxiansuo/html/kehuxinxi1.jsp");
			return modelAndView;
		}
    
	/**
	 * 分页查询所有用户跟踪信息
	 * 
	 * @param 
	 */
	@RequestMapping("/findUsertracking")
	public void pageFindSale(PageUtil page,String statu,String select,HttpServletResponse response,HttpServletRequest request){
		if(!"".equals(select) && select!=null){
			//获取员工ID
			String idString = (String) request.getSession().getAttribute("staffs");
			String[] id = idString.split(",");
			page.setPageSize(5);
			if(idString!=null){
			Integer count = usertrackingService.findCount(id,statu);
			if(count != null){
				page.setRowCount(count);
				//根据员工ID来查找销售机会
				List<Usertracking> usertrackings = usertrackingService.findByName(select,statu,id,page.getEndIndex(), page.getBeginIndex());
				List<UsertrackUtil> usertrackUtils = new ArrayList<>();
				if(usertrackings!=null){
			for (Usertracking usertracking : usertrackings) {
				UsertrackUtil usertrackUtil = new UsertrackUtil();
				usertrackUtil.setUsertracking(usertracking);
				usertrackUtils.add(usertrackUtil);
			}
			}
			int i = 0;
			for (UsertrackUtil usertrackUtil : usertrackUtils) {
				if(usertrackUtil.getUsertracking().getRecordId()!=null){
					Record record=recordService.findById(usertrackUtil.getUsertracking().getRecordId()); 
					if(record!=null){
						usertrackUtils.get(i).setRecord(record.getRecordName());
					}
				}
				if(usertrackUtil.getUsertracking().getStaffId()!=null){
					Staff staff=staffSerivce.findById(usertrackUtil.getUsertracking().getStaffId()); 
					if(staff!=null){
						usertrackUtils.get(i).setStaff(staff.getStaff_name());
					}
				}
				if(usertrackUtil.getUsertracking().getRankId()!=null){
					Rank rank=rankService.findById(usertrackUtil.getUsertracking().getRankId()); 
					if(rank!=null){
						usertrackUtils.get(i).setRank(rank.getRankName());
					}
				}
				if(usertrackUtil.getUsertracking().getCreateDate()!=null){
					String sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(usertrackUtil.getUsertracking().getCreateDate());
					if(sdf!=null){
						usertrackUtils.get(i).setShijian(sdf);
					}
				}
				i++;
			}
			response.setContentType("text/json; charset=UTF-8");
			InteractiveUtil.WriteJsonForPage(usertrackUtils, page, response);
			}	
			}
		}else{
			//获取员工ID
			String idString = (String) request.getSession().getAttribute("staffs");
			String[] id = idString.split(","); 
			page.setPageSize(5);
			if(idString!=null){
			Integer count = usertrackingService.findCount(id,statu);
			if(count != null){
				page.setRowCount(count);
				//根据员工ID来查找销售机会
				
				List<Usertracking> usertrackings = usertrackingService.pageFind(id,statu,page.getEndIndex(), page.getBeginIndex());
				List<UsertrackUtil> usertrackUtils = new ArrayList<>();
				if(usertrackings!=null){
			for (Usertracking usertracking : usertrackings) {
				UsertrackUtil usertrackUtil = new UsertrackUtil();
				usertrackUtil.setUsertracking(usertracking);
				usertrackUtils.add(usertrackUtil);
			}
			}
			int i = 0;
			for (UsertrackUtil usertrackUtil : usertrackUtils) {
				if(usertrackUtil.getUsertracking().getRecordId()!=null){
					Record record=recordService.findById(usertrackUtil.getUsertracking().getRecordId()); 
					if(record!=null){
						usertrackUtils.get(i).setRecord(record.getRecordName());
					}
				}
				if(usertrackUtil.getUsertracking().getStaffId()!=null){
					Staff staff=staffSerivce.findById(usertrackUtil.getUsertracking().getStaffId()); 
					if(staff!=null){
						usertrackUtils.get(i).setStaff(staff.getStaff_name());
					}
				}
				if(usertrackUtil.getUsertracking().getRankId()!=null){
					Rank rank=rankService.findById(usertrackUtil.getUsertracking().getRankId()); 
					if(rank!=null){
						usertrackUtils.get(i).setRank(rank.getRankName());
					}
				}
				
				if(usertrackUtil.getUsertracking().getCreateDate()!=null){
						String sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(usertrackUtil.getUsertracking().getCreateDate());
						if(sdf!=null){
							usertrackUtils.get(i).setShijian(sdf);
						}
					}
				i++;
			}
	 		response.setContentType("text/json; charset=UTF-8");
			InteractiveUtil.WriteJsonForPage(usertrackUtils, page, response);
			}	
			}
		}	
	}
	
	/**
	 * 分页查询所有企业跟踪信息
	 * 
	 * @param 
	 */
	@RequestMapping("/findUsertracking1")
	public void pageFindSale1(PageUtil page,String statu,String select,HttpServletResponse response,HttpServletRequest request){
		if(!"".equals(select) && select!=null){
			//获取员工ID
			String idString = (String) request.getSession().getAttribute("staffs");
			String[] id = idString.split(",");
			page.setPageSize(5);
			if(idString!=null){
			Integer count = usertrackingService.findCount(id,statu);
			if(count != null){
				page.setRowCount(count);
				//根据员工ID来查找销售机会
				 
				List<Usertracking> usertrackings = usertrackingService.findByName(select,statu,id,page.getEndIndex(), page.getBeginIndex());
				List<UsertrackUtil> usertrackUtils = new ArrayList<>();
				if(usertrackings!=null){
			for (Usertracking usertracking : usertrackings) {
				UsertrackUtil usertrackUtil = new UsertrackUtil();
				usertrackUtil.setUsertracking(usertracking);
				usertrackUtils.add(usertrackUtil);
			}
			}
			int i = 0;
			for (UsertrackUtil usertrackUtil : usertrackUtils) {
				if(usertrackUtil.getUsertracking().getRecordId()!=null){
					Record record=recordService.findById(usertrackUtil.getUsertracking().getRecordId()); 
					if(record!=null){
						usertrackUtils.get(i).setRecord(record.getRecordName());
					}
				}
				if(usertrackUtil.getUsertracking().getStaffId()!=null){
					Staff staff=staffSerivce.findById(usertrackUtil.getUsertracking().getStaffId()); 
					if(staff!=null){
						usertrackUtils.get(i).setStaff(staff.getStaff_name());
					}
				}
				if(usertrackUtil.getUsertracking().getRankId()!=null){
					Rank rank=rankService.findById(usertrackUtil.getUsertracking().getRankId()); 
					if(rank!=null){
						usertrackUtils.get(i).setRank(rank.getRankName());
					}
				}
				if(usertrackUtil.getUsertracking().getCreateDate()!=null){
					String sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(usertrackUtil.getUsertracking().getCreateDate());
					if(sdf!=null){
						usertrackUtils.get(i).setShijian(sdf);
					}
				}
				i++;
			}
			response.setContentType("text/json; charset=UTF-8");
			InteractiveUtil.WriteJsonForPage(usertrackUtils, page, response);
			}	
			}
		}else{
			//获取员工ID
			String idString = (String) request.getSession().getAttribute("staffs");
			String[] id = idString.split(","); 
			page.setPageSize(5);
			if(idString!=null){
			Integer count = usertrackingService.findCount(id,statu);
			if(count != null){
				page.setRowCount(count);
				//根据员工ID来查找销售机会
				
				List<Usertracking> usertrackings = usertrackingService.pageFind(id,statu,page.getEndIndex(), page.getBeginIndex());
				List<UsertrackUtil> usertrackUtils = new ArrayList<>();
				if(usertrackings!=null){
			for (Usertracking usertracking : usertrackings) {
				UsertrackUtil usertrackUtil = new UsertrackUtil();
				usertrackUtil.setUsertracking(usertracking);
				usertrackUtils.add(usertrackUtil);
			}
			}
			int i = 0;
			for (UsertrackUtil usertrackUtil : usertrackUtils) {
				if(usertrackUtil.getUsertracking().getRecordId()!=null){
					Record record=recordService.findById(usertrackUtil.getUsertracking().getRecordId()); 
					if(record!=null){
						usertrackUtils.get(i).setRecord(record.getRecordName());
					}
				}
				if(usertrackUtil.getUsertracking().getStaffId()!=null){
					Staff staff=staffSerivce.findById(usertrackUtil.getUsertracking().getStaffId()); 
					if(staff!=null){
						usertrackUtils.get(i).setStaff(staff.getStaff_name());
					}
				}
				if(usertrackUtil.getUsertracking().getRankId()!=null){
					Rank rank=rankService.findById(usertrackUtil.getUsertracking().getRankId()); 
					if(rank!=null){
						usertrackUtils.get(i).setRank(rank.getRankName());
					}
				}
				
				if(usertrackUtil.getUsertracking().getCreateDate()!=null){
						String sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(usertrackUtil.getUsertracking().getCreateDate());
						if(sdf!=null){
							usertrackUtils.get(i).setShijian(sdf);
						}
					}
				i++;
			}
	 		response.setContentType("text/json; charset=UTF-8");
			InteractiveUtil.WriteJsonForPage(usertrackUtils, page, response);
			}	
			}
		}	
	}
	
	/**
	 * 用户跟踪新增
	 */
 	@RequestMapping("/insertUsertracking")
 	@ResponseBody
 	public String insertUsertracking(HttpServletResponse response, Usertracking usertracking,HttpServletRequest request){
 		if(usertracking!=null){
 			usertracking.setCreateDate(new Date());
 				Boolean result=usertrackingService.save(usertracking);
 				if(result){
 					return  "success";
 				}
 				else{
 					return  "error";
 				}
 		}
 		return  "error";
 	}
 	
 	
 	/**
	 * 销售机会编辑
	 */
 	@RequestMapping("/updateUsertracking")
 	@ResponseBody
 	public void updateSale(HttpServletResponse response, Usertracking usertracking,HttpServletRequest request){
 		if(usertracking!=null){
 		Boolean reuslt=usertrackingService.update(usertracking);
 		if(reuslt){
			InteractiveUtil.WriteStringForHtml("编辑成功", response);
		}
		else{
			InteractiveUtil.WriteStringForHtml("编辑失败", response);
		}
 		InteractiveUtil.WriteStringForHtml("编辑失败", response);
 	}
 	}
	/**
	 * 批量删除
	 * @param id
	 * @return
	 */
	@RequestMapping("deleteManyId")
	public  void deleteManyId(Integer id,HttpServletResponse response){
				if(id!=null){
				boolean reuslt= usertrackingService.deleteById(id);
			
			if(reuslt){
				InteractiveUtil.WriteStringForHtml("删除成功", response);
			}
			else{
				InteractiveUtil.WriteStringForHtml("删除失败", response);
			}
				}
		InteractiveUtil.WriteStringForHtml("删除失败", response);
	}
	@RequestMapping("/excelOrder")
	@SuppressWarnings("deprecation")
	@ResponseBody
	public void excelOrder(HttpServletResponse response){
         
            //第一步，创建一个webbook，对应一个Excel文件  
            HSSFWorkbook wb = new HSSFWorkbook();  
            //第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet  
            HSSFSheet sheet = wb.createSheet("客户跟踪记录");  
            //第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制  
            HSSFRow row = sheet.createRow(0);  
            //第四步，创建单元格样式：居中  
            HSSFCellStyle style = wb.createCellStyle();  
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
            //第五步，创建表头单元格，并设置样式  
            HSSFCell cell;  
  
            cell = row.createCell((short) 0);  
            cell.setCellValue("客户姓名");  
            cell.setCellStyle(style);  
  
            cell = row.createCell((short) 1);  
            cell.setCellValue("事件类型");  
            cell.setCellStyle(style);  
  
            cell = row.createCell((short) 2);  
            cell.setCellValue("信息描述");  
            cell.setCellStyle(style);  
  
            cell = row.createCell((short) 3);  
            cell.setCellValue("创建日期");  
            cell.setCellStyle(style);  
            	//第六步，写入实体数据，实际应用中这些数据从数据库得到  
	         
            List<Usertracking> usertrackings = usertrackingService.findByAll();
			       for (int i = 0; i < usertrackings.size(); i++) {
			    	  row = sheet.createRow(i+1);
		                row.createCell((short) 0).setCellValue(usertrackings.get(i).getClientName());  
		                row.createCell((short) 1).setCellValue(usertrackings.get(i).getRecordId());  
		                row.createCell((short) 2).setCellValue(usertrackings.get(i).getInformation());  
		                row.createCell((short) 3).setCellValue(usertrackings.get(i).getCreateDate());  
		      
				} 
	            try {
	            	   
	            	   
	            	   
	            	   /**  输出信息，导出excel
	            	    * 
	            	    *  response.setContentType("application/vnd.ms-excel;charset=UTF-8");
	            	    response.setHeader("Content-Type", "application/octet-stream");
	            	    *  OutputStream out = response.getOutputStream();
	            	    wb.write(out);
	            	    out.close();
	            	    */
	            		SimpleDateFormat DateTime=new SimpleDateFormat("yyMMddHHmm");
	            	   String fileName = "跟踪表_" + DateTime.format(new Date()) + ".xls";  
	                   response.setContentType("application/vnd.ms-excel");  
	                   response.setCharacterEncoding("utf-8");  
	                   response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gbk"), "iso8859-1"));  
	             
	                   OutputStream os = response.getOutputStream();  
	                   wb.write(os);  
	                   os.flush();  
	                   }catch(Exception e){  
	                       e.printStackTrace();  
	                   }  
  
}
	/**
	 * 按名字模糊查询
	 */
	/*@RequestMapping("/findName")
	public void findSaleId(String select,PageUtil page,HttpServletRequest request,HttpServletResponse response) {
		//获取员工ID
				String idString = (String) request.getSession().getAttribute("staffs");
				page.setPageSize(5);
				if(idString!=null){
				Integer count = usertrackingService.findCount();
				if(count != null){
					page.setRowCount(count);
					//根据员工ID来查找销售机会
					String[] id = idString.split(","); 
					List<Usertracking> usertrackings = usertrackingService.findByName(select,id,page.getEndIndex(), page.getBeginIndex());
					List<UsertrackUtil> usertrackUtils = new ArrayList<>();
					if(usertrackings!=null){
				for (Usertracking usertracking : usertrackings) {
					UsertrackUtil usertrackUtil = new UsertrackUtil();
					usertrackUtil.setUsertracking(usertracking);
					usertrackUtils.add(usertrackUtil);
				}
				}
				int i = 0;
				for (UsertrackUtil usertrackUtil : usertrackUtils) {
					if(usertrackUtil.getUsertracking().getRecordId()!=null){
						Record record=recordService.findById(usertrackUtil.getUsertracking().getRecordId()); 
						if(record!=null){
							usertrackUtils.get(i).setRecord(record.getRecordName());
						}
					}
					if(usertrackUtil.getUsertracking().getStaffId()!=null){
						Staff staff=staffSerivce.findById(usertrackUtil.getUsertracking().getStaffId()); 
						if(staff!=null){
							usertrackUtils.get(i).setStaff(staff.getStaff_name());
						}
					}
					if(usertrackUtil.getUsertracking().getRankId()!=null){
						Rank rank=rankService.findById(usertrackUtil.getUsertracking().getRankId()); 
						if(rank!=null){
							usertrackUtils.get(i).setRank(rank.getRankName());
						}
					}
					i++;
				}
				response.setContentType("text/json; charset=UTF-8");
				InteractiveUtil.WriteJsonForPage(usertrackUtils, page, response);
				}	
				}	 
	}*/
	
	/**
	 * 跟踪信息批量导入
	 * @param file
	 * @throws IOException 
	 */
	@RequestMapping("/excelImportByuser")
	public void excelImportByStaff(@RequestParam("file") CommonsMultipartFile file,String statu,HttpServletRequest request,HttpServletResponse response) throws IOException{
		System.out.println(statu);
		if(!file.isEmpty()){
			String picture1 = FileUtil.fileUploe(file);
			if(!"图片上传失败".equals(picture1)){
				List<String> strings = exiclUtilByStaff("E:/img/"+picture1,request,statu);
				for (String string : strings) {
					System.out.println(string);
				}
				if(strings.size() != 0){
					InteractiveUtil.WriteJsonForHtml(strings, response);
					return;
				}
			}
		}
		InteractiveUtil.WriteStringForHtml("批量导入失败，请检查文件格式", response);
	}
public List<String> exiclUtilByStaff(String path,HttpServletRequest request,String statu){
		
		Staff staff = (Staff)request.getSession().getAttribute("staff");
		Integer id = staff.getStaff_id();
        // 直接读取文件:
        String filePath = path;
        File file = new File(filePath);
        InputStream is;
        XSSFSheet sheetMain;
        List<String> strings = new ArrayList<>();
        try {
        	 is = new FileInputStream(file);
             XSSFWorkbook wb = new XSSFWorkbook(is);
            // 读取第一个Sheet
            sheetMain = wb.getSheetAt(0);
            is.close();

            // 总共的行数
            int rowLens = sheetMain.getLastRowNum();
            int colLens = 4;
            int errCnt = 0;
            XSSFRow row = null;
            XSSFCell cell = null;
            String content = "";
            
            List<Record> record = recordService.listAll();
            List<Rank> ranks = rankService.listAll();
            List<Usertracking> usertrackings = new ArrayList<>();
            exc:for (int rowCount = 1; rowCount <= rowLens; rowCount++) {
            	Usertracking usertracking =new Usertracking();
                System.out.println("读取行：" + rowCount);
                row = sheetMain.getRow(rowCount);
                if (row != null) {
                	boolean a = true;
                    cc:for (int colCount = 0; colCount < colLens; colCount++) {
                        cell = row.getCell((short) colCount);
                        content = getCellValue(cell).trim();
                       
                        if (content == "") {
                            a = false;
                            strings.add("行 ：" + rowCount + "；列 ：" + colCount + "的内容：为空保存失败");
                            break cc;
                        }
                        if(colCount == 0){
                        	usertracking.setClientName(content);
                        }else if(colCount == 2){
                    		int deid = 0;
                    		for (Record records : record) {
								if(content.equals(records.getRecordName())){
									usertracking.setRecordId(records.getRecordId());
									deid++;
								}
							}
                    		if(deid == 0){
                    			a = false;
                                strings.add("行 ：" + rowCount + "；列 ：" + colCount + "的内容：类型不存在保存失败");
                                break cc;
                    		}
                        	
                        }else if(colCount==3){
                        	usertracking.setInformation(content);
                        	usertracking.setCreateDate(new Date());
                        	usertracking.setStaffId(id);
                        	usertracking.setStatu(statu);
                        }else if(colCount == 1){
                        	int i = 0;
                        	for (Rank rank : ranks) {
								if(rank.getRankName().equals(content)){
									usertracking.setRankId(rank.getRankId());
									i++;
								}
							}
                        	if(i == 0){
                        		a = false;
                                strings.add("行 ：" + rowCount + "；列 ：" + colCount + "的内容：客户级别不存在保存失败");
                                break cc;
                        	}
                        }
                        
                    }
                	if(a){
                		boolean b = usertrackingService.save(usertracking);
                    	if(b){
                    		strings.add("行 ：" + rowCount + "的内容：保存成功");
                    	}else{
                    		strings.add("行 ：" + rowCount + "的内容：保存失败");
                    	}
                	}
                    	
                }
                usertrackings.add(usertracking);
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return strings;
    }

public static String getCellValue(XSSFCell cell) {
    if (cell != null) {
        switch (cell.getCellType()) {
        case XSSFCell.CELL_TYPE_BLANK:
            return "";
        case XSSFCell.CELL_TYPE_NUMERIC:
            String strValue = String.valueOf(cell.getNumericCellValue());
            if (strValue != null && strValue.indexOf(".") != -1
                    && strValue.indexOf("E") != -1) {
                try {
                    return new DecimalFormat().parse(strValue).toString();
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            } else {
                if (strValue.endsWith(".0")) {
                    return strValue.substring(0, strValue.indexOf(".0"));
                } else {
                    return strValue;
                }
            }
        case XSSFCell.CELL_TYPE_STRING:
            return (cell.getStringCellValue() + "").trim();
        case XSSFCell.CELL_TYPE_FORMULA:
            return (cell.getCellFormula() + "").trim();
        case XSSFCell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue() + "";
        case XSSFCell.CELL_TYPE_ERROR:
            return cell.getErrorCellValue() + "";
        }
    }
    return "";
}
	
}
